#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into oe_dm.dm_attendance
select *
from oe_dws.dws_attendance_daycount;

insert into oe_dm.dm_leave
select *
from oe_dws.dws_leave_daycount;

insert into oe_dm.dm_absent
select a.date_code,
       a.class_id,
       a.content,
       a.studying_student_count,
       a.studying_student_count - a.morning_attendance_count -
       coalesce(l.morning_leave_count, 0)                                       as morning_absent_count,
       (a.studying_student_count - a.morning_attendance_count -
        coalesce(l.morning_leave_count, 0)) * 1.00 / a.studying_student_count   as morning_absent_ratio,
       a.studying_student_count - a.afternoon_attendance_count -
       coalesce(l.afternoon_leave_count, 0)                                     as afternoon_absent_count,
       (a.studying_student_count - a.afternoon_attendance_count -
        coalesce(l.afternoon_leave_count, 0)) * 1.00 / a.studying_student_count as afternoon_absent_ratio,
       a.studying_student_count - a.evening_attendance_count -
       coalesce(l.evening_leave_count, 0)                                       as evening_absent_count,
       (a.studying_student_count - a.evening_attendance_count -
        coalesce(l.evening_leave_count, 0)) * 1.00 / a.studying_student_count   as evening_absent_ratio
from oe_dws.dws_attendance_daycount a
         left join oe_dws.dws_leave_daycount l on a.class_id = l.class_id;"